﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Xml.Linq;
using System.Data.Odbc;

namespace SONORA2012
{
	public class Utilities
	{
		static string dbDSNName = "SNRDB";
		static string dbUserName = "PROGRAMMER";
		static string dbUserPassword = "apr";
		static string SQLConnStr = null;
		static string SQLCommand = null;
		string MSG = null;
		string TableName = null;
		
		public bool Execute(ref XDocument DataBox)
		{
			MSG = null;
			string SQLCommandString = null;
			string command = null;
			List<string> columns = new List<string>();
			List<string> values = new List<string>();
			List<string> wheres = new List<string>();
			List<string> orders = new List<string>();
			DataTable queryDataTable = new DataTable();
			XElement newMessageElement;

			var icommands = from tables in DataBox.Descendants("table")
							where tables.Attribute("name").Value == TableName
							from commands in tables.Descendants("command")
							select commands;
			foreach (XElement icommand in icommands)
			{
				command = icommand.Attribute("name").Value;
			}

			var icolumns = from tables in DataBox.Descendants("table")
						   where tables.Attribute("name").Value == TableName
						   from clmns in tables.Descendants("columns")
						   select clmns;
			foreach (XElement icolumn in icolumns)
			{
				columns.Add(icolumn.Attribute("name").Value);
			}

			var ivalues = from tables in DataBox.Descendants("table")
						  where tables.Attribute("name").Value == TableName
						  from vls in tables.Descendants("values")
						  select vls;
			foreach (XElement ivalue in ivalues)
			{
				values.Add(ivalue.Attribute("name").Value);
			}

			var iwhrs = from tables in DataBox.Descendants("table")
						where tables.Attribute("name").Value == TableName
						from whrs in tables.Descendants("values")
						select whrs;
			foreach (XElement iwhr in iwhrs)
			{
				wheres.Add(iwhr.Attribute("name").Value);
			}

			var iorders = from tables in DataBox.Descendants("table")
						  where tables.Attribute("name").Value == TableName
						  from ordrs in tables.Descendants("orders")
						  select ordrs;
			foreach (XElement iorder in iorders)
			{
				orders.Add(iorder.Attribute("name").Value);
			}

			switch (command)
			{
				case "SELECT":
					{
						if (columns.Count > 0)
						{
							SQLCommandString = "SELECT ";

							foreach (string column in columns)
							{
								SQLCommandString += column.Trim() + ",";
							}

							SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1) + " FROM " + TableName;

							if (wheres.Count > 0)
							{
								SQLCommandString += " WHERE ";

								foreach (string where in wheres)
								{
									SQLCommandString += where.Trim() + " ";
								}

								SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
							}

							if (orders.Count > 0)
							{
								SQLCommandString += " ORDER ";

								foreach (string order in orders)
								{
									SQLCommandString += order.Trim() + ",";
								}

								SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
							}

							SQLCommandString = SQLCommandString.Trim();
						}
						else
						{
							newMessageElement = new XElement("message",
																new XAttribute("name", "lang_list_of_column_is_empty"),
																new XAttribute("type", "ERROR")
															);
							DataBox.Element("databox").Elements("messages").Single().Add(newMessageElement);
							return false;
						}

						try
						{
							SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
							OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
							OdbcCommand newODBCCom = new OdbcCommand();
							newODBCCom.Connection = newODBCConn;
							newODBCCom.CommandText = SQLCommandString;

							newODBCConn.Open();
							OdbcDataAdapter newDA = new OdbcDataAdapter(newODBCCom);
							newDA.Fill(queryDataTable);
							newODBCConn.Close();

							System.IO.StringWriter writer = new System.IO.StringWriter();
							queryDataTable.WriteXml(writer, XmlWriteMode.WriteSchema, false);

							newMessageElement = new XElement("message",
																new XAttribute("name", "lang_selecting_is_successful"),
																new XAttribute("type", "INFO")
															);
							DataBox.Element("databox").Elements("messages").Single().Add(newMessageElement);
						}
						catch
						{
							newMessageElement = new XElement("message",
																new XAttribute("name", "lang_selecting_is_not_successful"),
																new XAttribute("type", "ERROR")
															);
							return false;
						}

						break;
					}
			}

			return true;
		}

		public bool Select(List<string> ColumnList, List<string> WhereList, List<string> OrderList)
		{
			string MSG = null;
			string SQLCommandString = null;

			if (ColumnList.Count > 0)
			{
				SQLCommandString = "SELECT ";

				foreach (string column in ColumnList)
				{
					SQLCommandString += column.Trim() + ",";
				}

				SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1) + " FROM " + TableName;

				if (WhereList.Count > 0)
				{
					SQLCommandString += " WHERE ";

					foreach (string where in WhereList)
					{
						SQLCommandString += where.Trim() + " ";
					}

					SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
				}

				if (OrderList.Count > 0)
				{
					SQLCommandString += " ORDER ";

					foreach (string order in OrderList)
					{
						SQLCommandString += order.Trim() + ",";
					}

					SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
				}

				SQLCommandString = SQLCommandString.Trim();
			}
			else
			{
				MSG = "lang_list_of_column_is_empty";
				return false;
			}

			try
			{
				SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
				OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
				OdbcCommand newODBCCom = new OdbcCommand();
				newODBCCom.Connection = newODBCConn;
				newODBCCom.CommandText = SQLCommandString;

				newODBCConn.Open();
				newODBCCom.ExecuteNonQuery();
				newODBCConn.Close();

				MSG = "lang_selecting_is_successful";
			}
			catch
			{
				MSG = "lang_selecting_is_not_successful";
				return false;
			}

			return true;
		}

		public bool Insert(List<string> ColumnList, List<string> ValueList, out string MSG)
		{
			MSG = null;
			string SQLCommandString = null;

			if (ColumnList.Count == ValueList.Count && ColumnList.Count > 0)
			{
				SQLCommandString = "INSERT INTO " + TableName + "(";

				foreach (string column in ColumnList)
				{
					SQLCommandString += column.Trim() + ",";
				}

				SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1) + ") VALUES(";

				foreach (string value in ValueList)
				{
					SQLCommandString += value.Trim() + ",";
				}

				SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1) + ")";

				SQLCommandString = SQLCommandString.Trim();
			}
			else
			{
				if (ColumnList.Count == ValueList.Count)
					MSG = "lang_list_of_column_and_list_of_value_are_not_equal";
				if (ColumnList.Count > 0)
					MSG = "lang_list_of_column_and_list_of_value_are_empty";

				return false;
			}

			try
			{
				SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
				OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
				OdbcCommand newODBCCom = new OdbcCommand();
				newODBCCom.Connection = newODBCConn;
				newODBCCom.CommandText = SQLCommandString;

				newODBCConn.Open();
				newODBCCom.ExecuteNonQuery();
				newODBCConn.Close();

				MSG = "lang_inserting_is_successful";
			}
			catch (Exception exx)
			{
				MSG = "lang_inserting_is_not_successful" + " --- Hata mesajı : " + exx.Message.ToString();
				return false;
			}

			return true;
		}

		public bool Update(List<string> ColumnValueList, List<string> WhereList)
		{
			string MSG = null;
			string SQLCommandString = null;

			if (ColumnValueList.Count > 0)
			{
				SQLCommandString = "UPDATE " + TableName + " SET ";

				foreach (string columnvalue in ColumnValueList)
				{
					SQLCommandString += columnvalue.Trim() + ",";
				}

				SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);

				if (WhereList.Count > 0)
				{
					SQLCommandString += " WHERE ";

					foreach (string where in WhereList)
					{
						SQLCommandString += where.Trim() + " ";
					}

					SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
				}

				SQLCommandString = SQLCommandString.Trim();
			}
			else
			{
				MSG = "lang_list_of_column_is_empty";

				return false;
			}

			try
			{
				SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
				OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
				OdbcCommand newODBCCom = new OdbcCommand();
				newODBCCom.Connection = newODBCConn;
				newODBCCom.CommandText = SQLCommandString;

				newODBCConn.Open();
				newODBCCom.ExecuteNonQuery();
				newODBCConn.Close();

				MSG = "lang_updating_is_successful";
			}
			catch
			{
				MSG = "lang_updating_is_not_successful";
				return false;
			}

			return true;
		}

		public bool Delete(List<string> ColumnList, List<string> WhereList)
		{
			string MSG = null;
			string SQLCommandString = null;

			SQLCommandString = "DELETE FROM " + TableName;

			if (WhereList.Count > 0)
			{
				SQLCommandString += " WHERE ";

				foreach (string where in WhereList)
				{
					SQLCommandString += where.Trim() + " ";
				}

				SQLCommandString = SQLCommandString.Substring(0, SQLCommandString.Count() - 1);
			}

			SQLCommandString = SQLCommandString.Trim();

			try
			{
				SQLConnStr = "Dsn=" + dbDSNName + ";uid=" + dbUserName + ";pwd=" + dbUserPassword;
				OdbcConnection newODBCConn = new OdbcConnection(SQLConnStr);
				OdbcCommand newODBCCom = new OdbcCommand();
				newODBCCom.Connection = newODBCConn;
				newODBCCom.CommandText = SQLCommandString;

				newODBCConn.Open();
				newODBCCom.ExecuteNonQuery();
				newODBCConn.Close();

				MSG = "lang_deleting_is_successful";
			}
			catch
			{
				MSG = "lang_deleting_is_not_successful";
				return false;
			}

			return true;
		}
	}
}